[Oracle Debug] 實現用regexp_substr分解資料成多個欄位


Posted by RedPanda56 on 2022-06-17

* 目標

給定資料如下:將此字串分割成三個欄位

SELECT '1^^2' AS temp_value
FROM dual

* 使用regexp_substr (方法1)

WITH temp AS
(
    SELECT '1^2^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

aa bb cc
1 2 3

* 使用regexp_substr (方法2)

當遇到有「空值」的時候,方法1會造成資料和欄位錯置

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

aa bb cc
1 3

* 使用regexp_substr (方法2 改良)

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1, null, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2, null, 1) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3, null, 1) AS cc
FROM temp

* 輸出結果

aa bb cc
1 3

#oracle #SQL







Related Posts

 React-[入門篇]- React 起手式 |CRA建立React專案

React-[入門篇]- React 起手式 |CRA建立React專案

漏洞與修補留言板 XSS 和 SQL Injection

漏洞與修補留言板 XSS 和 SQL Injection

[MTR04] W2 D1 JavaScript 基礎

[MTR04] W2 D1 JavaScript 基礎


Comments